﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.IO;

namespace InsideDL
{
    public partial class frmFTPRateInterest : MISControl.frmMatrix//Form
    {
        //protected string ftp = string.Empty;
        //protected string id = string.Empty;
        //protected string pw = string.Empty;
        //protected string[] downList = null;
        protected string saveFilePath
        {
            get
            {
                return ucALMFilePath.Txt;
            }
        }
        protected string rateInterestPath
        {
            get
            {
                return System.IO.Path.GetDirectoryName(ucRate.Txt);
            }
        }
        protected Dictionary<string, object> sheetSource = null;
        protected string[] rateInterest = { "FTP量息加工-{0}.xls", "FTP量率加工-{0}(C_L).XLS" };
        protected string[] newRateInterest = { string.Empty,string.Empty};
        protected string[] eisSheets = { "F公庫","F財管" , "F企金" };
        protected string[] refreshSheets = { "公庫", "財管", "企金" };
        protected Microsoft.Office.Interop.Excel.Application exlAPP = null;
        public frmFTPRateInterest()
        {
            InitializeComponent();
            init();
            setProperty();

            ucExit.ExecuteClick += new EventHandler(ucClickFacade);
            ucExecute.ExecuteClick += new EventHandler(ucClickFacade);
        }
        
        protected void init()
        {
            Utility.LoadConfigValue("FTPCollection");
        }

        protected void setProperty()
        {
            ftp = Utility.GetConfigValue("ALM");
            id = Utility.GetConfigValue("ftpid");
            pw = Utility.GetConfigValue("ftppw");
        }

        /// <summary>
        /// 險了檢查檔案放上來沒，還要再檢查日期是否為上月的最後一天
        /// </summary>
        /// <returns></returns>
        protected bool checkFileInFTPSite()
        {
            string lastMonthDay = Utility.BeforeMonthLastDay(Utility.GetCurrentYM());
            string[] fileList = Utility.GetFTPFileList(ftp, id, pw);
            downList = (from fl in fileList
                        where fl.ToUpper().IndexOf(".XLS") > 0
                        select fl).ToArray();
            if (downList.Length != 2)
            {
                return false;
            }
            bool ok = false;
            foreach (string last in downList)
            {
                if (last.Length > 24)
                {
                    if (last.Substring(16, 8).Equals(lastMonthDay))
                    {
                        ok = true;
                    }                
                }
            }
            return ok;
        }

        protected bool downloadFileFromFtp()
        {
            return Utility.DownloadFile(ftp, id, pw, downList, saveFilePath);
        }

        private void button1_Click(object sender, EventArgs e)
        {
            checkFileInFTPSite();
//            string str = @"ASP.NET Global.asax 檔案可以和 ASP Global.asa 檔案共同存在。
//您可以在 WYSIWYG 設計工具或在 Notepad 中建立 Global.asax 檔案，或者像組件 (Assembly) 一樣當作您部署於應用程式 \Bin 目錄中的已編譯類別來建立。
//然而，後者的情形中，您仍然需要一個參考至組件的 Global.asax 檔案。";
           
//            textBox1.Text = str;
            //getDataTables();
            string connectionString = @"Driver={Microsoft Excel Driver (*.xls)};DBQ=D:\temp\Transfer_Margin_20101130.XLS;";
            string p_sql = @"
         SELECT 
            板塊 
            ,LEFT([GL Account Number],10) AS [科子細目]
            ,MID([GL Account Number],11) AS [名稱]
            ,[Average Balance]
            ,[Interest Rate]
        FROM [L_all$B5:J20000]
        WHERE 板塊 IN ('1','A','C')
        ";

            ADODB.ConnectionClass adoCN = new ADODB.ConnectionClass();
            ADODB.RecordsetClass adoRS = new ADODB.RecordsetClass();
            adoCN.Open(connectionString, string.Empty, string.Empty, 0);
            adoCN.CursorLocation = ADODB.CursorLocationEnum.adUseClient;
            adoRS.Open(p_sql, adoCN, ADODB.CursorTypeEnum.adOpenDynamic
                , ADODB.LockTypeEnum.adLockPessimistic, 1);
        }

        protected void getDataTables()
        {
            sheetSource = new Dictionary<string, object>();
            object dt = null;
            string fullPath = string.Format(@"{0}\{1}", saveFilePath
                , downList[0]);
            MISData.DataFTPRateInterest ftpRate = new InsideDL.MISData.DataFTPRateInterest();
            dt = ftpRate.GetEIS("'1'" , fullPath);
            sheetSource.Add(string.Format("EIS_{0}",eisSheets[0])
                , dt);

            dt = ftpRate.GetEIS("'A'", fullPath);
            sheetSource.Add(string.Format("EIS_{0}",eisSheets[1])
                , dt);

            dt = ftpRate.GetEIS("'C','D','E'", fullPath);
            sheetSource.Add(string.Format("EIS_{0}",eisSheets[2])
                , dt);

            fullPath = string.Format(@"{0}\{1}", saveFilePath
                , downList[1]);
            dt = ftpRate.GetMargin(downList[0], fullPath);
            sheetSource.Add("Margin", dt);

        }


        protected override void execute()
        {
            if (!checkFileInFTPSite())
            {
                ShowStatus("確認FTP檔案");
                return;
            }

            if (!downloadFileFromFtp())
            {
                ShowStatus("取得FTP檔案");
                return;
            }
            exlAPP = new Microsoft.Office.Interop.Excel.Application();
            ShowStatus("產生率、息檔案");
            getDataTables();
            ShowStatus("產生新的率、息檔");
            createNewFile();
            openInsideFile();
            ShowStatus("率、息資料填入對內檔案");
            clearAndFillRateFile();
            clearAndFillInterestFile();
            saveCloseXLS();
            exlAPP.Quit();
            showMessage(this.Text, "完成！");
            ShowStatus("");
            releaseObject(exlAPP);
        }

      

        //把對內檔案開啟,要把量率量息的兩個資料貼到指定的sheet
        void openInsideFile()
        {
            //50FTP資料→息 51輸入區→率
            OpenExcelFile(ucInsideFile.Txt);
        }

        void saveCloseXLS()
        {
            exlAPP.AlertBeforeOverwriting = false;
            exlAPP.DisplayAlerts = false;            
            for (int i = 1; i <= exlAPP.Workbooks.Count; i++)
            {                
                exlAPP.Workbooks[i].Save();
            }
        }

        /// <summary>
        /// 打開要工作的EXCEL 表格
        /// </summary>
        protected void OpenExcelFile(string p_xls)
        {
            object oMissing = System.Reflection.Missing.Value;
            exlAPP.Workbooks.Open(p_xls, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing
                , oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
        }
        
        /// <summary>
        /// 量息加工
        /// </summary>
        void clearAndFillInterestFile()
        {
            string target = string.Format("{0}\\{1}"
                , rateInterestPath
                , newRateInterest[0]);
            OpenExcelFile(target);
            ADODB.RecordsetClass dt = null;
            Microsoft.Office.Interop.Excel.Worksheet wsht = null;
            Microsoft.Office.Interop.Excel.Range rng = null;
            for(int i=0;i<eisSheets.Length;i++)
            {
                wsht = GetSheetObject(eisSheets[i]) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                rng = wsht.get_Range("B5:J40000", Type.Missing);
                rng.ClearContents();
                rng = wsht.get_Range("B5", Type.Missing);
                dt = sheetSource[string.Format("EIS_{0}", eisSheets[i])]
                    as  ADODB.RecordsetClass;
                rng.CopyFromRecordset(dt, Type.Missing, Type.Missing);
                wsht = GetSheetObject(refreshSheets[i]) as
                        Microsoft.Office.Interop.Excel.Worksheet;
                refreshPivotTables(wsht);
            }
            wsht = GetSheetObject("資料") as
                   Microsoft.Office.Interop.Excel.Worksheet;
            rng = wsht.get_Range("B1:AD40", Type.Missing);
            rng.Copy(Type.Missing);
            copyToInsideFile("50FTP資料","B1");
        }

        /// <summary>
        /// 率和息的檔案copy 到對內檔案中去
        /// </summary>
        void copyToInsideFile(string p_sheet,string p_range)
        {            
            findWorkBook(ucInsideFile.GetSelectFileName());
            Microsoft.Office.Interop.Excel.Worksheet wsht = 
                GetSheetObject(p_sheet) as
                 Microsoft.Office.Interop.Excel.Worksheet;
            Microsoft.Office.Interop.Excel.Range rng1 =
                wsht.get_Range(p_range, Type.Missing);
            rng1.PasteSpecial(
                        Microsoft.Office.Interop.Excel.XlPasteType.xlPasteValues
                        , Microsoft.Office.Interop.Excel.XlPasteSpecialOperation.xlPasteSpecialOperationNone
                        , false
                        , false);
        }

        /// <summary>
        /// 清除量率的檔案!
        /// </summary>
        void clearAndFillRateFile()
        {
            string target = string.Format("{0}\\{1}"
                , rateInterestPath
                , newRateInterest[1]);
            OpenExcelFile(target);
            ADODB.RecordsetClass dt = null;
            Microsoft.Office.Interop.Excel.Worksheet wsht = GetSheetObject("1AC") as
                    Microsoft.Office.Interop.Excel.Worksheet;
            Microsoft.Office.Interop.Excel.Range rng = wsht.get_Range("B7:g10000", Type.Missing);
            rng.ClearContents();
            rng = wsht.get_Range("B7", Type.Missing);
            dt = sheetSource[string.Format("Margin", eisSheets[0])]
                as ADODB.RecordsetClass;
            rng.CopyFromRecordset(dt, Type.Missing, Type.Missing);
            wsht = GetSheetObject("樞紐") as
                 Microsoft.Office.Interop.Excel.Worksheet;
            refreshPivotTables(wsht);
            wsht = GetSheetObject("資料") as
                 Microsoft.Office.Interop.Excel.Worksheet;
            rng = wsht.get_Range("D49:O58", Type.Missing);
            rng.Copy(Type.Missing);

            copyToInsideFile("51輸入區", "B50");
        }

        void refreshPivotTables(Microsoft.Office.Interop.Excel.Worksheet p_wsht)
        {
            Microsoft.Office.Interop.Excel.PivotTable pt = p_wsht.PivotTables(1)
        as Microsoft.Office.Interop.Excel.PivotTable;
            pt.PivotCache().Refresh();
        }

        /// <summary>
        /// 取回在EXCEL中所指定的Sheet
        /// </summary>
        /// <param name="p_sheetName"></param>
        /// <returns></returns>
        protected object GetSheetObject(string p_sheetName)
        {
            Microsoft.Office.Interop.Excel.Worksheet ws = null;
            for (int i = 1; i <= exlAPP.Sheets.Count; i++)
            {
                ws = (Microsoft.Office.Interop.Excel.Worksheet)exlAPP.Worksheets.get_Item(i);
                if (ws.Name.Trim().Equals(p_sheetName))
                {
                    break;
                }
            }
            return ws;
        }

        void findWorkBook(string p_workBook)
        {
            foreach (Microsoft.Office.Interop.Excel.Workbook wk
                in exlAPP.Workbooks)
            {
                if (wk.Name.Equals(p_workBook))
                {
                    wk.Activate();
                    break;
                }
            }

        }

        private void createNewFile()
        {
            string[] newFiles = { ucInterest.Txt, ucRate.Txt };
            //建立新的檔案
            for (int i = 0; i < rateInterest.Length; i++)
            {
                newRateInterest[i] = string.Format(rateInterest[i], Utility.GetTWYM()); 
                string target = string.Format("{0}\\{1}"
                    , rateInterestPath
                    , newRateInterest[i]);
                //把上個月的copy 一份新的
                File.Copy(newFiles[i], target, true);                                        
            }
        }

        protected void ShowStatus(string p_msg)
        {
            showStatus(p_msg, lblStatus);
        }

    }
}
